package com.hzb;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Date;

/**
 * 功能描述
 *
 * @author 和振斌
 * @date 2020/8/1
 * @return
 */

public class ExcelReadTest {
    String Path="F:\\springBoot_SSM\\springboot文件上传下载\\excel_poi\\src\\main\\resources\\测试1.xlsx";

    @Test
    public void testRead03() throws Exception {
        //获取文件流
        FileInputStream inputStream = new FileInputStream(Path);
        //创建一个工作簿
        Workbook workbook=new HSSFWorkbook(inputStream);
        //获取工作表工作表
        Sheet sheet = workbook.getSheetAt(0);
        Row row =sheet.getRow(0);
        Cell cell = row.getCell(1);
        System.out.println(cell.getStringCellValue());
        inputStream.close();
    }

    @Test
    public void testRead07() throws Exception {
        //获取文件流
        FileInputStream inputStream = new FileInputStream(Path);
        //创建一个工作簿
        Workbook workbook=new XSSFWorkbook(inputStream);
        //获取工作表工作表
        Sheet sheet = workbook.getSheetAt(0);
        Row row =sheet.getRow(0);
        Cell cell = row.getCell(0);
        System.out.println(cell.getStringCellValue());
       inputStream.close();
    }

@Test
    public void testCellType() throws Exception {

        //获取输入流
        FileInputStream inputStream = new FileInputStream(Path);
        //创建一个工作簿
        Workbook workbook=new XSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0);
        //获取标题内容
        //读取第一行
        Row rowTitle = sheet.getRow(0);
        if(rowTitle!=null){
            //查看有几个列
            int cells = rowTitle.getPhysicalNumberOfCells();
            for(int cellNum=0;cellNum<cells;cellNum++){
                Cell cell = rowTitle.getCell(cellNum);
                if(cell!=null){
                    CellType cellType = cell.getCellType();
                    String cellValue = cell.getStringCellValue();
                    System.out.print(cellValue+"|");
                }
            }
            System.out.println();
        }
        /**获取表中的内容*/
    int rowCount = sheet.getPhysicalNumberOfRows();
    for(int rowNum=1;rowNum< rowCount;rowNum++){
        Row rowData = sheet.getRow(rowNum);
        if(rowData!=null){
            //读取列
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for(int cellNum=0;cellNum<cellCount;cellNum++){
                System.out.print("["+(rowNum+1)+"-"+(cellNum+1)+"]");
                Cell cell = rowData.getCell(cellNum);
                //匹配列的类型
                if(cell!=null){
                    CellType cellType = cell.getCellType();
                    String cellValue="";
                    switch (cellType){
                        /**字符串*/
                        case STRING:
                            System.out.println("【String】");
                             cellValue = cell.getStringCellValue();
                             break;
                        case BOOLEAN:
                            /**布尔类型*/
                            System.out.println("【Boolean】");
                            cellValue=String.valueOf(cell.getBooleanCellValue());
                            break;
                            case _NONE:
                            /**为空*/
                            System.out.println("【BLANK】");
                            break;
                        case NUMERIC:
                            /**数字(日期、普通数字*/
                            System.out.println("【NUMBER】");
                            if(HSSFDateUtil.isCellDateFormatted(cell)){
                                /**日期*/
                                System.out.println("【日期】");
                                Date date = cell.getDateCellValue();
                                String s = new DateTime(date).toString("yyyy-MM-dd");
                            }else {
                                /**普通数字*/
                                System.out.println("【字符串输出】");
                               cellValue=cell.toString();
                            }
                            break;
                        case ERROR:
                            System.out.println("【类型错误】");
                          break;
                    }
                    System.out.println(cellValue);
                }
            }
            inputStream.close();
        }
    }

}

}
